﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using DBSchemaViewer.BackEnd.Entities;
using DBSchemaViewer.BackEnd.Entities.MetaEntities;


namespace DBSchemaViewer.BackEnd.ModelXML
{
    public class SQLScriptXMLDAL
    {
        private DataStoreXML _dataStoreXML = DataStoreXML.Instance;

        
        /// <summary>
        /// Simplified XML format that would help XSL templates to be quite easy to 
        /// implement.
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public string GenerateSimplifiedXMLForSQLScriptGeneration(string databaseName)
        {            
            XElement tablesSubTree    = generateTablesXMLSubStree();
            XElement relationsSubTree = generateRelationsXMLSubtree();

            XElement root = new XElement("database"
                                            , new XElement("name", databaseName)
                                            , tablesSubTree
                                            , relationsSubTree
                                            );

            return root.ToString();
        }


        public string GenerateSimplifiedXMLForTabularReportGeneration(int dbEngine)
        {
            XElement tablesSubTree = generateTablesXMLSubStree();
            XElement relationsSubTree = generateRelationsXMLSubtree();

            XElement root = new XElement("database"
                                            , new XElement("dbEngine", dbEngine.ToString())
                                            , tablesSubTree
                                            , relationsSubTree
                                            );

            return root.ToString();
        }

        /// <summary>
        /// Table subtree
        /// </summary>
        /// <returns></returns>
        private XElement generateTablesXMLSubStree()
        {
            /*
              Type of XML Expected:
              
                  <tables>
                    <table>
                    <name>author</name>
                    <columns>
                      <column>
                        <name>ID</name>
                        <type>4</type>
                      </column>

                      <column>
                        <name>Name</name>
                        <type>5</type>
                        <length>50</length>
                      </column>
                    </columns>
                    <pk>
                      <field>ID</field>      
                    </pk>
                  </table>
             
             */

            XElement xmlGeneratedSubTree = null;

            TableDAL tableDAL = new TableDAL();

            EntityTable[] tables = tableDAL.GetTables();

            var tableNodes = from p in tables
                                        select new XElement("table"
                                                , new XElement("name", p.Name)
                                                , new XElement("columns"
                                                    , from pcolumns in p.Fields
                                                    select new XElement("column"
                                                      , new XElement("name", pcolumns.FieldName)
                                                      , new XElement("type", pcolumns.Type)
                                                      , new XElement("length", pcolumns.Length)
                                                      )
                                                  )
                                                , new XElement("pk"
                                                    , from pcolumns in p.Fields
                                                        where pcolumns.IsPrimaryKey == true
                                                        select new XElement("field", pcolumns.FieldName)                                                        
                                                  )
                                            );

            xmlGeneratedSubTree = new XElement("tables",
                                                  tableNodes);


            return xmlGeneratedSubTree;
        }

        /// <summary>
        /// Relations subtree
        /// </summary>
        /// <returns></returns>
        private XElement generateRelationsXMLSubtree()
        {
            /* --> Type of XML expected
              <relations>
                <relation>
                  <tableMaster>author</tableMaster>
                  <tableDetail>book</tableDetail>
                  <master>
                    <field>ID</field>
                  </master>
                  <detail>
                    <field>author</field>        
                  </detail>
                </relation>
              </relations>
            */

            XElement xmlGeneratedSubTree = null;

            RelationDAL relationDAL = new RelationDAL();
            TableDAL tableDAL = new TableDAL();
            

            EntityRelation[] relations = relationDAL.GetRelations();
            

            var relationNodes = from p in relations
                             select new XElement("relation"
                                     , new XElement("tableMaster", tableDAL.GetTable(p.TableOriginID).Name)
                                     , new XElement("tableDetail", tableDAL.GetTable(p.TableDestinationID).Name)
                                     , new XElement("columns")
                                     , new XElement("master",
                                     
                                            from pFieldRelations in p.RelationFields                                     
                                                select   new XElement("field", tableDAL.GetTableField(p.TableOriginID, pFieldRelations.FieldOrigin).FieldName)                                           
                                        )

                                     , new XElement("detail",
                                            from pFieldRelations in p.RelationFields
                                               select new XElement("field", tableDAL.GetTableField(p.TableDestinationID, pFieldRelations.FieldDestination).FieldName)                                           
                                       )

                                 );

            xmlGeneratedSubTree = new XElement("relations",
                                                  relationNodes);

            return xmlGeneratedSubTree;
        }

    }
}
